package dao;

import utils.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * @author 李夏
 * @version 1.0
 */
public class HolInfoMapper {
    //管理员/宿管/教师获取某楼栋假期信息总数
    public static Integer getHolOneBuild(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as hol_info_count  \n"+
                "from hol  \n"+
                "where delete_flag=0 and build_id=?";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("hol_info_count");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员获取所有假期信息总数
    public static Integer queryHolAllCount() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol \n"+
                "from hol  \n"+
                "where delete_flag=0";
        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员/宿管/教师获取某楼栋在校学生总数
    public static Integer getHolOneBuildOn(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol  \n"+
                "from hol  \n"+
                "where delete_flag=0 and build_id=? and hol_status='在校'";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员/宿管/教师获取某楼栋离校学生总数
    public static Integer getHolOneBuildLeave(Integer buildId) throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol  \n"+
                "from hol  \n"+
                "where delete_flag=0 and build_id=? and hol_status='离校'";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setInt(1, buildId);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
//管理员获取所有楼栋在校学生总数
    public static Integer getHolAllOn() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol  \n"+
                "from hol  \n"+
                "where delete_flag=0 and  hol_status='在校'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;

    }
//管理员获取所有楼栋离校学生总数
    public static Integer getHolAllLeave() throws SQLException {
        Connection connection = DBUtil.getConnection();
        ResultSet resultSet = null;
        PreparedStatement preparedStatement = null;
        String sql = "select count(*) as total_hol  \n"+
                "from hol  \n"+
                "where delete_flag=0 and  hol_status='离校'";

        preparedStatement = connection.prepareStatement(sql);
        resultSet = preparedStatement.executeQuery();
        Integer count = null;
        if (resultSet.next()) {
            count = resultSet.getInt("total_hol");
        }

        DBUtil.close(connection,preparedStatement,resultSet);


        return count;
    }
}
